package dao.impl;

import dao.BaseDao;
import dao.ManagementDao;
import entity.Management;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ManagementDaoImpl extends BaseDao implements ManagementDao {

    @Override
    public List<Management> show(String building_name, int floor, String room_number, String bed_number)
            throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement statement = null;
        ResultSet set = null;
        List<Management> list = new ArrayList<>();

        try {

            String sql = "SELECT d.bed_id, b.building_name, d.bed_number, x.room_number, x.floor, " +
                    "s.`name`, s.phone, s.student_time " +
                    "FROM students s JOIN dorm_beds d ON s.bed_id = d.bed_id " +
                    "JOIN dorm_rooms x ON d.room_id = x.room_id " +
                    "JOIN dorm_buildings b ON x.building_id = b.building_id " +
                    "WHERE b.building_name = ? AND x.floor = ? AND x.room_number = ? AND d.bed_number = ?";

            statement = conn.prepareStatement(sql);
            statement.setString(1, building_name);
            statement.setInt(2, floor);
            statement.setString(3, room_number);
            statement.setString(4, bed_number);

            set = statement.executeQuery();

            while (set.next()) {
                Management management = new Management();
                management.setBed_id(set.getInt("bed_id"));
                management.setBuilding_name(set.getString("building_name"));
                management.setBed_number(set.getString("bed_number"));
                management.setRoom_number(set.getString("room_number"));
                management.setFloor(set.getInt("floor"));
                management.setName(set.getString("name"));
                management.setPhone(set.getString("phone"));
                management.setStudent_time(set.getString("student_time"));
                list.add(management);
            }
            return list;
        } finally {
            if (set != null) set.close();
            if (statement != null) statement.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int addBedAndRoom(String bed_number, String room_number, int floor)
            throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement roomStmt = null;
        PreparedStatement bedStmt = null;

        try {

            conn.setAutoCommit(false);

            // 添加房间
            String roomSql = "INSERT INTO dorm_rooms(room_number, floor) VALUES (?, ?)";
            roomStmt = conn.prepareStatement(roomSql, Statement.RETURN_GENERATED_KEYS);
            roomStmt.setString(1, room_number);
            roomStmt.setInt(2, floor);
            int roomResult = roomStmt.executeUpdate();

            // 获取刚插入的房间ID
            int roomId = 0;
            ResultSet rs = roomStmt.getGeneratedKeys();
            if (rs.next()) {
                roomId = rs.getInt(1);
            }

            // 添加床位（关联房间ID）
            String bedSql = "INSERT INTO dorm_beds(bed_number, room_id) VALUES (?, ?)";
            bedStmt = conn.prepareStatement(bedSql);
            bedStmt.setString(1, bed_number);
            bedStmt.setInt(2, roomId);
            int bedResult = bedStmt.executeUpdate();

            conn.commit();
            return roomResult + bedResult;

        } catch (SQLException e) {
            if (conn != null) conn.rollback();
            throw e;
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.close();
            }
            if (roomStmt != null) roomStmt.close();
            if (bedStmt != null) bedStmt.close();
        }
    }

    @Override
    public int Delete(int bed_id) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement bedStmt = null;
        PreparedStatement studentStmt = null;

        try {

            conn.setAutoCommit(false);

            // 先解除学生关联
            String studentSql = "UPDATE students SET bed_id = NULL WHERE bed_id = ?";
            studentStmt = conn.prepareStatement(studentSql);
            studentStmt.setInt(1, bed_id);
            int studentResult = studentStmt.executeUpdate();

            // 再删除床位
            String bedSql = "DELETE FROM dorm_beds WHERE bed_id = ?";
            bedStmt = conn.prepareStatement(bedSql);
            bedStmt.setInt(1, bed_id);
            int bedResult = bedStmt.executeUpdate();

            conn.commit();
            return studentResult + bedResult;

        } catch (SQLException e) {
            if (conn != null) conn.rollback();
            throw e;
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.close();
            }
            if (bedStmt != null) bedStmt.close();
            if (studentStmt != null) studentStmt.close();
        }
    }

    @Override
    public List<Management> Buildings() throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement statement = null;
        ResultSet set = null;
        List<Management> list = new ArrayList<>();

        try {

            String sql = "SELECT DISTINCT building_name FROM dorm_buildings";
            statement = conn.prepareStatement(sql);
            set = statement.executeQuery();

            while (set.next()) {
                Management management = new Management();
                management.setBuilding_name(set.getString("building_name"));
                list.add(management);
            }
            return list;
        } finally {
            if (set != null) set.close();
            if (statement != null) statement.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public List<Management> Floor() throws SQLException, ClassNotFoundException {
      getConnection();
        PreparedStatement statement = null;
        ResultSet set = null;
        List<Management> list = new ArrayList<>();

        try {

            String sql = "SELECT DISTINCT floor FROM dorm_rooms";
            statement = conn.prepareStatement(sql);
            set = statement.executeQuery();

            while (set.next()) {
                Management management = new Management();
                management.setFloor(set.getInt("floor"));
                list.add(management);
            }
            return list;
        } finally {
            if (set != null) set.close();
            if (statement != null) statement.close();
            if (conn != null) conn.close();
        }
    }
}